package com.zengdada.authority.service.impl;

import com.alibaba.fastjson.JSON;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.zengdada.authority.mapper.PublicSqlMapper;
import com.zengdada.authority.utils.CommonUtils;
import com.zengdada.authority.utils.HEXUtil;
import com.zengdada.authority.utils.mapper.Page;
import com.zengdada.authority.utils.mapper.Sort;
import com.zengdada.authority.utils.where.MapperWhere;
import com.zengdada.authority.utils.where.Where;
import com.zengdada.authority.utils.where.result.MapperAnalysisResultMerge;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @Auther: 12614
 * @Contact: 1261438511@qq.com
 * @Date: 2018/12/23 19:13
 * @Description: 通用查询接口
 */
@Service
public class PublicSqlService {

    private static final Map<String, List<String>> SLQ_FIELD_CACHE = new HashMap<>();
    private static final Map<String, Long> SLQ_FIELD_CACHE_TIME = new HashMap<>();

    @Autowired
    PublicSqlMapper sqlMapper;
    @Autowired
    JdbcTemplate jdbcTemplate;


    public PageInfo<Map<String, Object>> selectData(String sql, Where searches) {
        return selectData(sql, searches, null, null);
    }

    public PageInfo<Map<String, Object>> selectData(String sql, String searchStr) {
        return selectData(sql, searchStr, null, null);
    }

    /**
     * 通用查询
     *
     * @param sql    查询sql 字段名满足驼峰命名法
     * @param where 条件  空查询请用 formula ISNULL 或者 NOTNULL
     * @param page   分页情况
     * @param sorts  排序情况
     * @return
     */
    public PageInfo<Map<String, Object>> selectData(String sql, Where where, Page page, List<Sort> sorts) {
//        if (params == null) params = new ArrayList<>();
        if (sorts == null) {
            sorts = new ArrayList<>();
        }
        String sqlHEX = HEXUtil.stringHEX(sql, HEXUtil.Type.MD5);
        StringBuilder orderStr = new StringBuilder();
        // 检查缓存中是否有语句对应的的字段
        if (!SLQ_FIELD_CACHE.keySet().contains(sqlHEX)) {
            setFieldCache(sqlHEX, selectSqlFields(sql));
        }
        // 过滤空查询 (空查询请用 formula ISNULL 或者 NOTNULL)
//        params = params.stream().filter(i -> (i.getValue() != null && !"".equals(i.getValue())) || i.getFormulaE() == PublicSearch.Formula.IS_NULL || i.getFormulaE() == PublicSearch.Formula.IS_NOT_NULL)
//                .collect(Collectors.toList());
//        for (PublicSearch search : params) { // 校验查询语句是否合法
//            if (!getFieldCache(sqlHEX).contains(CommonUtils.humpToLine(search.getField()).toLowerCase()) && !getFieldCache(sqlHEX).contains(CommonUtils.humpToLine(search.getField())))
//                throw new RuntimeException(String.format("语句中不存在的查询字段 ‘%s’ ！！", search.getField()));
//        }
        MapperAnalysisResultMerge analysis = new MapperAnalysisResultMerge();
        if (where != null) {
            analysis = JSON.parseObject(JSON.toJSONString(where), MapperWhere.class).setFields(getFieldCache(sqlHEX)).analysisMerge();
        }

        for (Sort sort : sorts) {
            if (sort != null && sort.isNotNull()) {
                //校验排序语句是否合法
                if (!getFieldCache(sqlHEX).contains(CommonUtils.humpToLine(sort.getField()).toLowerCase()) && !getFieldCache(sqlHEX).contains(CommonUtils.humpToLine(sort.getField()))) {
                    throw new RuntimeException(String.format("语句中不存在的排序字段 ‘%s’ ！！", sort.getField()));
                }
                orderStr.append(sort.getOrderDetailed()).append(",");
            }
        }
        if (orderStr.length() > 0 && orderStr.charAt(orderStr.length() - 1) == ',') {
            orderStr.deleteCharAt(orderStr.length() - 1);
        }
        if (page != null) {
            PageHelper.startPage(page.getPage(), page.getPageSize());
        }
        List<Map<String, Object>> list = sqlMapper.selectDataEnhance(Collections.singletonList("*"), sql, analysis, orderStr.length() > 0 ? orderStr.toString() : null);
        // 字段名转驼峰命名
        CommonUtils.depthTransformHump(list);
        return new PageInfo<>(list);
    }

    /**
     * 通用查询
     *
     * @param sql       查询sql 字段名满足驼峰命名法
     * @param searchStr 条件  满足PublicSearch 的 json 字符串  空查询请用 formula ISNULL 或者 NOTNULL
     * @param page      分页情况
     * @param sorts     排序情况
     * @return
     */
    public PageInfo<Map<String, Object>> selectData(String sql, String searchStr, Page page, List<Sort> sorts) {
        Where where;
        try {
            where = Where.formJsonString(searchStr);
        } catch (Exception e) {
            throw new RuntimeException(String.format("查询信息（%s）转化失败：%s", searchStr, e.getMessage()));
        }
        return selectData(sql, where, page, sorts);
    }


    private List<String> selectSqlFields(String sql) {
        List<String> result = new ArrayList<>();
        SqlRowSet rowSet;
        try {
            rowSet = jdbcTemplate.queryForRowSet(String.format("SELECT MY_TMP_TABLE.* FROM ( %s) MY_TMP_TABLE WHERE ROWNUM = 0", sql));
        } catch (DataAccessException e) {
            throw new RuntimeException("查询列信息失败，数据语句语法错误！！" + e.getMessage());
        }
        SqlRowSetMetaData metaData = rowSet.getMetaData();
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            result.add(metaData.getColumnName(i).toUpperCase());
        }
        return result;
    }

    // 缓存字段数据 并清理闲置的数据
    private synchronized void setFieldCache(String sqlHEX, List<String> fields) {
        SLQ_FIELD_CACHE.put(sqlHEX, fields);
        long currentTimeMillis = System.currentTimeMillis();
        SLQ_FIELD_CACHE_TIME.put(sqlHEX, currentTimeMillis);
        if (SLQ_FIELD_CACHE.size() > 50) {
            for (Iterator<Map.Entry<String, Long>> it = SLQ_FIELD_CACHE_TIME.entrySet().iterator(); it.hasNext(); ) {
                Map.Entry<String, Long> entry = it.next();
                if (currentTimeMillis - entry.getValue() > 1000 * 60 * 60) {
                    SLQ_FIELD_CACHE.remove(entry.getKey());
                    it.remove();
                }
            }
        }
    }

    // 获取字段信息并更新最近使用时间
    private synchronized List<String> getFieldCache(String sqlHEX) {
        SLQ_FIELD_CACHE_TIME.put(sqlHEX, System.currentTimeMillis());
        return SLQ_FIELD_CACHE.get(sqlHEX);
    }


    /**
     * 执行sql查询单个记录
     *
     * @param sql 有效sql
     * @return
     */
    public Map<String, Object> selectOneBySQL(String sql) {
        return CommonUtils.depthTransformHumpNew(sqlMapper.selectOneBySQL(sql));
    }


}
